package com.ruc.deke.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Component;

import com.ruc.deke.dao.UserDAO;
import com.ruc.deke.model.DBRecord;
import com.ruc.deke.model.User;
import com.ruc.deke.utils.ParseUtil;

@Component(value = "userDAOImpl")
public class UserDAOImpl implements UserDAO {

	@Resource(name = "dataSource_rucdbcourse")
	private DataSource dataSource;

	@Override
	public ArrayList<DBRecord> getUserByName(String name) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from DB_COURSE_USER where name = '" + name + "'";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public void addUser(User user) {
		int result = 0;
		Statement statement = null;
		Connection connection = null;
		DBRecord record = new DBRecord();
		record.setField("NAME", user.getName());
		record.setField("PASSWD", user.getPasswd());
		record.setField("EMAIL", user.getEmail());
		record.setField("REALNAME", user.getRealname());
		record.setField("UNO", user.getUno());
		record.setField("MNO", user.getMno());
		record.setField("GRADE", user.getGrade());
		record.setField("CLASSES", user.getClasses());
		record.setField("ANO", user.getUno() + user.getSequence());// 通过学校和数据库序号进行确定

		try {
			String sql = ParseUtil.toInsertSQL("DB_COURSE_USER", record);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			result = statement.executeUpdate(sql);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}

	}

	@Override
	public ArrayList<DBRecord> getUserExpStatusByUserId(int userId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from USER_EXP_STATUS where userid = " + userId;
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}

	@Override
	public ArrayList<DBRecord> getUserExpStatusByUserIdAndModuleID(int userId, int moduleId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from USER_EXP_STATUS where userid = " + userId + " and moduleid = " + moduleId
				+ " order by expid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}

	//public ArrayList<DBRecord> getUserExpStatusByUserIdAndPaperId(int userId, String paperId);
	@Override
	public ArrayList<DBRecord> getUserExpStatusByUserIdAndPaperId(int userId, String paperId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from USER_EXP_STATUS where USERID = " + userId + " and PAPERID = '" + paperId
				+ "' order by expid";
		System.out.println("0000000"+sql+"11111111");
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}
	
	@Override
	public ArrayList<DBRecord> getUsersByAno(String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from DB_COURSE_USER where ANO = '" + ano + "' order by userid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}
//删除用户 zk
	@Override
	public String delUser(int userId) {
		Connection connection = null;
		Statement stmt = null;
		String result = "success";
		try {
			String sql1 = "delete from USER_TO_ROLE where USERID="+userId;
			String sql2 = "delete from USER_MODULE_SCORE where USERID="+userId;
			String sql3 = "delete from USER_EXP_STATUS where USERID="+userId;
			String sql4 = "delete from USER_EXP_STATUS1 where USERID="+userId;
			String sql5 = "delete from LOGS where USERID="+userId;
			String sql6 = "delete from DB_COURSE_USER where USERID="+userId;
			connection = dataSource.getConnection();
			stmt = connection.createStatement();
			stmt.executeUpdate(sql1);
			stmt.executeUpdate(sql2);
			stmt.executeUpdate(sql3);
			stmt.executeUpdate(sql4);
			stmt.executeUpdate(sql5);
			stmt.executeUpdate(sql6);
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			result = "failed";
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			result = "failed";
		}
		return result;
	}
//end
	@Override
	public String updateUser(DBRecord newRecord, DBRecord conditions) {
		Connection connection = null;
		Statement stmt = null;
		String result = "success";
		try {
			String sql = ParseUtil.toUpdataSQL("DB_COURSE_USER", newRecord, conditions);
			connection = dataSource.getConnection();
			stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			result = "failed";
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			result = "failed";
		}
		return result;
	}

	@Override
	public ArrayList<DBRecord> getUserScoresByModuleIdAndAno(String ano, int moduleid) {

		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		// String sql="select user_exp_status.userid,user_exp_status.realname "
		// + " from user_exp_status where user_exp_status.moduleid = "+moduleid;
		String sql = "select user_exp_status.expid,db_course_user.userid,user_exp_status.score"
				+ " from user_exp_status,db_course_user " + " where user_exp_status.userid = db_course_user.userid and "
				+ " user_exp_status.moduleid = " + moduleid + " and db_course_user.ano= '" + ano + "' "
				+ " order by user_exp_status.userid ";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> getUsersByModuleIdAndAno(String ano, int moduleId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select distinct user_exp_status.userid ,db_course_user.realname"
				+ " from user_exp_status,db_course_user" + " where user_exp_status.userid = db_course_user.userid and "
				+ " user_exp_status.moduleid = " + moduleId + " and db_course_user.ano = '" + ano + "' "
				+ " order by user_exp_status.userid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> haveSubmitedByAnoAndModule(int moduleId, String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select distinct user_exp_status.userid from user_exp_status,db_course_user "
				+ " where user_exp_status.userid = db_course_user.userid " + " and user_exp_status.moduleid = "
				+ moduleId + " and user_exp_status.estatus = 1 " + " and db_course_user.ano = '" + ano + "'";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}

	@Override
	public ArrayList<DBRecord> haveSubmitedAllByAnoAndModule(int moduleId, String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from user_exp_status,db_course_user "
				+ " where user_exp_status.userid = db_course_user.userid " + " and user_exp_status.moduleid = "
				+ moduleId + " and user_exp_status.estatus = 1 " + " and db_course_user.ano = '" + ano
				+ "' and db_course_user.status = 1 order by expid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
			System.out.println("-----------------------------################--------------------------------");
			System.out.println(recordsList.size());
			System.out.println("-----------------------------################--------------------------------");
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}

	@Override
	public ArrayList<DBRecord> haveSavedByAnoAndModule(int moduleId, String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select distinct user_exp_status.userid from user_exp_status,db_course_user "
				+ " where user_exp_status.userid = db_course_user.userid " + " and user_exp_status.moduleid = "
				+ moduleId + " and user_exp_status.estatus = 2 " + " and db_course_user.ano = '" + ano
				+ "' and db_course_user.status = 1 order by user_exp_status.userid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);

			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> getUserScoreByMoudleAndUserIdAndAno(int moduleId, int userId, String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = " select * from user_exp_status,db_course_user "
				+ " where user_exp_status.userid = db_course_user.userid "
				+ " and user_exp_status.estatus = 1 and user_exp_status.moduleid = " + moduleId
				+ " and db_course_user.ano = '" + ano + "' and db_course_user.userid = " + userId + " order by expid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> getRealUsersByAno(String ano) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from DB_COURSE_USER where ANO = '" + ano + "' and status = 1 order by userid";
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;
	}

	@Override
	public ArrayList<DBRecord> getScoreByModuleAndAno(String ano, int moduleId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select db_course_user.USERID,REALNAME,EMAIL,NAME,sum(SCORE) A from user_exp_status,db_course_user where user_exp_status.userid = db_course_user.userid and user_exp_status.moduleid ="
				+ moduleId + " and user_exp_status.estatus = 1 and db_course_user.ano = '" + ano
				+ "' and db_course_user.status = 1 group by db_course_user.USERID,REALNAME,EMAIL,NAME order by A DESC";
		System.out.println("hello");
		ArrayList<DBRecord> recordsList = new ArrayList<DBRecord>();
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			System.out.println("-----" + dataSource);
			recordsList = ParseUtil.toDBRecord(resultSet);
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// e.printStackTrace();
				System.out.println("exception:" + e.getMessage());
			}
		}
		return recordsList;

	}

	@Override
	public Boolean deleteRec(int userId) {
		int executeState = 0;
		Connection connection = null;
		Statement statement = null;
		// ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		/**
		 * 1 删除user_exp_status中的记录
		 */
		try {
			String sql = "delete from user_exp_status where userid = " + userId;
			// String sql="delete from db_course_user where userid = " + userId;
			System.out.println(sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			executeState = statement.executeUpdate(sql);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}
		if (executeState == -1) {
			System.out.println("ERROR in 'delete from user_exp_status'!");
			return false;
		}

		/**
		 * 2 删除user_exp_status中的记录
		 */
		try {
			String sql = "delete from user_module_score where userid = " + userId;
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			executeState = statement.executeUpdate(sql);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}
		if (executeState == -1) {
			System.out.println("ERROR in 'delete from user_module_score'!");
			return false;
		}

		return true;
	}

}
